{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:16.776280Z",
     "end_time": "2024-06-18T16:36:17.300015Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\AppData\\Local\\Temp\\ipykernel_18848\\1889705378.py:10: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  trans = pd.read_csv('../data/bank/trans.csv', encoding='gbk')\n"
     ]
    }
   ],
   "source": [
    "# 1 数据整理\n",
    "# 1.1导入数据\n",
    "accounts = pd.read_csv('../data/bank/accounts.csv', encoding='gbk')\n",
    "card = pd.read_csv('../data/bank/card.csv', encoding='gbk')\n",
    "clients = pd.read_csv('../data/bank/clients.csv', encoding='gbk')\n",
    "disp = pd.read_csv('../data/bank/disp.csv', encoding='gbk')\n",
    "district = pd.read_csv('../data/bank/district.csv', encoding='gbk')\n",
    "loans = pd.read_csv('../data/bank/loans.csv', encoding='gbk')\n",
    "order = pd.read_csv('../data/bank/order.csv', encoding='gbk')\n",
    "trans = pd.read_csv('../data/bank/trans.csv', encoding='gbk')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:17.303015Z",
     "end_time": "2024-06-18T16:36:18.691199Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id        date  amount  duration  payments status  \\\n0     5314        1787  1993-07-05   96396        12      8033      B   \n1     5316        1801  1993-07-11  165960        36      4610      A   \n2     6863        9188  1993-07-28  127080        60      2118      A   \n3     5325        1843  1993-08-03  105804        36      2939      A   \n4     7240       11013  1993-09-06  274740        60      4579      A   \n\n   bad_good  \n0         1  \n1         0  \n2         0  \n3         0  \n4         0  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>status</th>\n      <th>bad_good</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>1993-07-05</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>B</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>1993-07-11</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>A</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>1993-07-28</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>A</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>1993-08-03</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>A</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>1993-09-06</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>A</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.2 生成被解释变量bad_good\n",
    "bad_good = {'B': 1, 'D': 1, 'A': 0, 'C': 2}\n",
    "loans['bad_good'] = loans['status'].map(bad_good)\n",
    "loans.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:18.693202Z",
     "end_time": "2024-06-18T16:36:18.706692Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id        date  amount  duration  payments status  \\\n0     5314        1787  1993-07-05   96396        12      8033      B   \n1     5316        1801  1993-07-11  165960        36      4610      A   \n2     6863        9188  1993-07-28  127080        60      2118      A   \n3     5325        1843  1993-08-03  105804        36      2939      A   \n4     7240       11013  1993-09-06  274740        60      4579      A   \n\n   bad_good  disp_id  client_id type sex  birth_date  district_id  \n0         1     2166       2166  所有者   女  1947-07-22           30  \n1         0     2181       2181  所有者   男  1968-07-22           46  \n2         0    11006      11314  所有者   男  1936-06-02           45  \n3         0     2235       2235  所有者   女  1940-04-20           14  \n4         0    13231      13539  所有者   男  1978-09-07           63  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>status</th>\n      <th>bad_good</th>\n      <th>disp_id</th>\n      <th>client_id</th>\n      <th>type</th>\n      <th>sex</th>\n      <th>birth_date</th>\n      <th>district_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>1993-07-05</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>B</td>\n      <td>1</td>\n      <td>2166</td>\n      <td>2166</td>\n      <td>所有者</td>\n      <td>女</td>\n      <td>1947-07-22</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>1993-07-11</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2181</td>\n      <td>2181</td>\n      <td>所有者</td>\n      <td>男</td>\n      <td>1968-07-22</td>\n      <td>46</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>1993-07-28</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>A</td>\n      <td>0</td>\n      <td>11006</td>\n      <td>11314</td>\n      <td>所有者</td>\n      <td>男</td>\n      <td>1936-06-02</td>\n      <td>45</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>1993-08-03</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2235</td>\n      <td>2235</td>\n      <td>所有者</td>\n      <td>女</td>\n      <td>1940-04-20</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>1993-09-06</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>A</td>\n      <td>0</td>\n      <td>13231</td>\n      <td>13539</td>\n      <td>所有者</td>\n      <td>男</td>\n      <td>1978-09-07</td>\n      <td>63</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.3 借款人的年龄、性别\n",
    "data2 = pd.merge(loans, disp, on='account_id', how='left')\n",
    "data2 = pd.merge(data2, clients, on='client_id', how='left')\n",
    "data2 = data2[data2.type == '所有者']\n",
    "data2.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:18.708691Z",
     "end_time": "2024-06-18T16:36:18.781361Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id        date  amount  duration  payments status  \\\n0     5314        1787  1993-07-05   96396        12      8033      B   \n1     5316        1801  1993-07-11  165960        36      4610      A   \n2     6863        9188  1993-07-28  127080        60      2118      A   \n3     5325        1843  1993-08-03  105804        36      2939      A   \n4     7240       11013  1993-09-06  274740        60      4579      A   \n\n   bad_good  disp_id  client_id  ...  A1    GDP      A4   A10    A11   A12  \\\n0         1     2166       2166  ...  30  16979   94812  81.8   9650  3.38   \n1         0     2181       2181  ...  46  14111  112709  73.5   8369  1.79   \n2         0    11006      11314  ...  45  12888   77917  53.5   8390  2.28   \n3         0     2235       2235  ...  14  31891  177686  74.8  10045  1.42   \n4         0    13231      13539  ...  63  11322   86513  50.5   8288  3.79   \n\n    A13  A14   A15   a16  \n0  3.67  100  15.7  14.8  \n1  2.31  117  12.7  11.6  \n2  2.89  132  13.3  13.6  \n3  1.71  135  18.6  17.7  \n4  4.52  110   9.0   8.4  \n\n[5 rows x 24 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>status</th>\n      <th>bad_good</th>\n      <th>disp_id</th>\n      <th>client_id</th>\n      <th>...</th>\n      <th>A1</th>\n      <th>GDP</th>\n      <th>A4</th>\n      <th>A10</th>\n      <th>A11</th>\n      <th>A12</th>\n      <th>A13</th>\n      <th>A14</th>\n      <th>A15</th>\n      <th>a16</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>1993-07-05</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>B</td>\n      <td>1</td>\n      <td>2166</td>\n      <td>2166</td>\n      <td>...</td>\n      <td>30</td>\n      <td>16979</td>\n      <td>94812</td>\n      <td>81.8</td>\n      <td>9650</td>\n      <td>3.38</td>\n      <td>3.67</td>\n      <td>100</td>\n      <td>15.7</td>\n      <td>14.8</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>1993-07-11</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2181</td>\n      <td>2181</td>\n      <td>...</td>\n      <td>46</td>\n      <td>14111</td>\n      <td>112709</td>\n      <td>73.5</td>\n      <td>8369</td>\n      <td>1.79</td>\n      <td>2.31</td>\n      <td>117</td>\n      <td>12.7</td>\n      <td>11.6</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>1993-07-28</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>A</td>\n      <td>0</td>\n      <td>11006</td>\n      <td>11314</td>\n      <td>...</td>\n      <td>45</td>\n      <td>12888</td>\n      <td>77917</td>\n      <td>53.5</td>\n      <td>8390</td>\n      <td>2.28</td>\n      <td>2.89</td>\n      <td>132</td>\n      <td>13.3</td>\n      <td>13.6</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>1993-08-03</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2235</td>\n      <td>2235</td>\n      <td>...</td>\n      <td>14</td>\n      <td>31891</td>\n      <td>177686</td>\n      <td>74.8</td>\n      <td>10045</td>\n      <td>1.42</td>\n      <td>1.71</td>\n      <td>135</td>\n      <td>18.6</td>\n      <td>17.7</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>1993-09-06</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>A</td>\n      <td>0</td>\n      <td>13231</td>\n      <td>13539</td>\n      <td>...</td>\n      <td>63</td>\n      <td>11322</td>\n      <td>86513</td>\n      <td>50.5</td>\n      <td>8288</td>\n      <td>3.79</td>\n      <td>4.52</td>\n      <td>110</td>\n      <td>9.0</td>\n      <td>8.4</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 24 columns</p>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.4 借款人居住地的经济状况\n",
    "data3 = pd.merge(data2, district, left_on='district_id', right_on='A1', how='left')\n",
    "data3.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:18.727878Z",
     "end_time": "2024-06-18T16:36:18.843375Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "       account_id       date type  amount balance     t_date\n10020           2 1994-01-05    贷  $1,100  $1,100 1993-02-26\n10021           2 1994-01-05    贷  $20236  $21336 1993-03-12\n10022           2 1994-01-05    贷  $3,700  $25036 1993-03-28\n10023           2 1994-01-05    贷     $14  $25050 1993-03-31\n10024           2 1994-01-05    贷  $20236  $45286 1993-04-12",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>type</th>\n      <th>amount</th>\n      <th>balance</th>\n      <th>t_date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10020</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$1,100</td>\n      <td>$1,100</td>\n      <td>1993-02-26</td>\n    </tr>\n    <tr>\n      <th>10021</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$20236</td>\n      <td>$21336</td>\n      <td>1993-03-12</td>\n    </tr>\n    <tr>\n      <th>10022</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$3,700</td>\n      <td>$25036</td>\n      <td>1993-03-28</td>\n    </tr>\n    <tr>\n      <th>10023</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$14</td>\n      <td>$25050</td>\n      <td>1993-03-31</td>\n    </tr>\n    <tr>\n      <th>10024</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$20236</td>\n      <td>$45286</td>\n      <td>1993-04-12</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.5 贷款前一年内的账户平均余额、余额的标准差、变异系数、平均收入和平均支出的比例\n",
    "data_4temp1 = pd.merge(loans[['account_id', 'date']]\n",
    "                       , trans[['account_id', 'type', 'amount', 'balance', 'date']]\n",
    "                       , on='account_id')\n",
    "data_4temp1.columns = ['account_id', 'date', 'type', 'amount', 'balance', 't_date']\n",
    "data_4temp1 = data_4temp1.sort_values(by=['account_id', 't_date'])\n",
    "data_4temp1['date'] = pd.to_datetime(data_4temp1['date'])\n",
    "data_4temp1['t_date'] = pd.to_datetime(data_4temp1['t_date'])\n",
    "data_4temp1.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:18.751367Z",
     "end_time": "2024-06-18T16:36:19.137078Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "       account_id       date type  amount balance     t_date  balance2  \\\n10020           2 1994-01-05    贷  $1,100  $1,100 1993-02-26      1100   \n10021           2 1994-01-05    贷  $20236  $21336 1993-03-12     21336   \n10022           2 1994-01-05    贷  $3,700  $25036 1993-03-28     25036   \n10023           2 1994-01-05    贷     $14  $25050 1993-03-31     25050   \n10024           2 1994-01-05    贷  $20236  $45286 1993-04-12     45286   \n\n       amount2  \n10020     1100  \n10021    20236  \n10022     3700  \n10023       14  \n10024    20236  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>type</th>\n      <th>amount</th>\n      <th>balance</th>\n      <th>t_date</th>\n      <th>balance2</th>\n      <th>amount2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10020</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$1,100</td>\n      <td>$1,100</td>\n      <td>1993-02-26</td>\n      <td>1100</td>\n      <td>1100</td>\n    </tr>\n    <tr>\n      <th>10021</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$20236</td>\n      <td>$21336</td>\n      <td>1993-03-12</td>\n      <td>21336</td>\n      <td>20236</td>\n    </tr>\n    <tr>\n      <th>10022</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$3,700</td>\n      <td>$25036</td>\n      <td>1993-03-28</td>\n      <td>25036</td>\n      <td>3700</td>\n    </tr>\n    <tr>\n      <th>10023</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$14</td>\n      <td>$25050</td>\n      <td>1993-03-31</td>\n      <td>25050</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>10024</th>\n      <td>2</td>\n      <td>1994-01-05</td>\n      <td>贷</td>\n      <td>$20236</td>\n      <td>$45286</td>\n      <td>1993-04-12</td>\n      <td>45286</td>\n      <td>20236</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对账户余额进行清洗\n",
    "data_4temp1['balance2'] = data_4temp1['balance'].map(lambda x: int(''.join(x[1:].split(','))))\n",
    "data_4temp1['amount2'] = data_4temp1['amount'].map(lambda x: int(''.join(x[1:].split(','))))\n",
    "data_4temp1.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.132079Z",
     "end_time": "2024-06-18T16:36:19.392740Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\AppData\\Local\\Temp\\ipykernel_18848\\3315624795.py:4: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  data_4temp2 = data_4temp1[data_4temp1['date'] > data_4temp1['t_date']][\n"
     ]
    },
    {
     "data": {
      "text/plain": "        account_id       date type  amount balance     t_date  balance2  \\\n127026       11362 1996-12-27    借    $129  $39766 1996-12-06     39766   \n127027       11362 1996-12-27    借  $10400  $29366 1996-12-07     29366   \n127028       11362 1996-12-27    借    $330  $29036 1996-12-07     29036   \n127029       11362 1996-12-27    借     $56  $28980 1996-12-08     28980   \n127030       11362 1996-12-27    借  $4,780  $24200 1996-12-10     24200   \n\n        amount2  \n127026      129  \n127027    10400  \n127028      330  \n127029       56  \n127030     4780  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>type</th>\n      <th>amount</th>\n      <th>balance</th>\n      <th>t_date</th>\n      <th>balance2</th>\n      <th>amount2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>127026</th>\n      <td>11362</td>\n      <td>1996-12-27</td>\n      <td>借</td>\n      <td>$129</td>\n      <td>$39766</td>\n      <td>1996-12-06</td>\n      <td>39766</td>\n      <td>129</td>\n    </tr>\n    <tr>\n      <th>127027</th>\n      <td>11362</td>\n      <td>1996-12-27</td>\n      <td>借</td>\n      <td>$10400</td>\n      <td>$29366</td>\n      <td>1996-12-07</td>\n      <td>29366</td>\n      <td>10400</td>\n    </tr>\n    <tr>\n      <th>127028</th>\n      <td>11362</td>\n      <td>1996-12-27</td>\n      <td>借</td>\n      <td>$330</td>\n      <td>$29036</td>\n      <td>1996-12-07</td>\n      <td>29036</td>\n      <td>330</td>\n    </tr>\n    <tr>\n      <th>127029</th>\n      <td>11362</td>\n      <td>1996-12-27</td>\n      <td>借</td>\n      <td>$56</td>\n      <td>$28980</td>\n      <td>1996-12-08</td>\n      <td>28980</td>\n      <td>56</td>\n    </tr>\n    <tr>\n      <th>127030</th>\n      <td>11362</td>\n      <td>1996-12-27</td>\n      <td>借</td>\n      <td>$4,780</td>\n      <td>$24200</td>\n      <td>1996-12-10</td>\n      <td>24200</td>\n      <td>4780</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据取数窗口提取交易数据\n",
    "import datetime\n",
    "\n",
    "data_4temp2 = data_4temp1[data_4temp1['date'] > data_4temp1['t_date']][\n",
    "    data_4temp1['date'] < data_4temp1['t_date'] + datetime.timedelta(days=365)]\n",
    "data_4temp2.tail()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.389741Z",
     "end_time": "2024-06-18T16:36:19.507281Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "             avg_balance  stdev_balance  cv_balance\naccount_id                                         \n2           32590.759259   12061.802206    0.370099\n19          25871.223684   15057.521648    0.582018\n25          56916.984496   21058.667949    0.369989\n37          36658.981308   20782.996690    0.566928\n38          31383.581818   10950.723180    0.348932",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>avg_balance</th>\n      <th>stdev_balance</th>\n      <th>cv_balance</th>\n    </tr>\n    <tr>\n      <th>account_id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2</th>\n      <td>32590.759259</td>\n      <td>12061.802206</td>\n      <td>0.370099</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>25871.223684</td>\n      <td>15057.521648</td>\n      <td>0.582018</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>56916.984496</td>\n      <td>21058.667949</td>\n      <td>0.369989</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>36658.981308</td>\n      <td>20782.996690</td>\n      <td>0.566928</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>31383.581818</td>\n      <td>10950.723180</td>\n      <td>0.348932</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.5.1 账户平均余额、余额的标准差、变异系数\n",
    "data_4temp3 = data_4temp2.groupby('account_id')['balance2'].agg([('avg_balance', 'mean'), ('stdev_balance', 'std')])\n",
    "data_4temp3['cv_balance'] = data_4temp3[['avg_balance', 'stdev_balance']].apply(lambda x: x.iloc[1] / x.iloc[0], axis=1)\n",
    "data_4temp3.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.428251Z",
     "end_time": "2024-06-18T16:36:19.532094Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "                   amount2\naccount_id type1          \n2          income   276514\n           out      153020\n19         income   254255\n           out      198020\n25         income   726479",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>amount2</th>\n    </tr>\n    <tr>\n      <th>account_id</th>\n      <th>type1</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">2</th>\n      <th>income</th>\n      <td>276514</td>\n    </tr>\n    <tr>\n      <th>out</th>\n      <td>153020</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">19</th>\n      <th>income</th>\n      <td>254255</td>\n    </tr>\n    <tr>\n      <th>out</th>\n      <td>198020</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <th>income</th>\n      <td>726479</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.5.2 平均支出和平均收入的比例\n",
    "type_dict = {'借': 'out', '贷': 'income'}\n",
    "data_4temp2['type1'] = data_4temp2['type'].map(type_dict)\n",
    "data_4temp4 = data_4temp2.groupby(['account_id', 'type1'])['amount2'].sum()\n",
    "data_4temp4 = data_4temp4.to_frame('amount2')\n",
    "data_4temp4.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.448806Z",
     "end_time": "2024-06-18T16:36:19.540084Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "type1         income       out  r_out_in\naccount_id                              \n2           276514.0  153020.0  0.553390\n19          254255.0  198020.0  0.778824\n25          726479.0  629108.0  0.865969\n37          386357.0  328541.0  0.850356\n38          154300.0  105091.0  0.681082",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type1</th>\n      <th>income</th>\n      <th>out</th>\n      <th>r_out_in</th>\n    </tr>\n    <tr>\n      <th>account_id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2</th>\n      <td>276514.0</td>\n      <td>153020.0</td>\n      <td>0.553390</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>254255.0</td>\n      <td>198020.0</td>\n      <td>0.778824</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>726479.0</td>\n      <td>629108.0</td>\n      <td>0.865969</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>386357.0</td>\n      <td>328541.0</td>\n      <td>0.850356</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>154300.0</td>\n      <td>105091.0</td>\n      <td>0.681082</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_4temp5 = pd.pivot_table(data_4temp4, values='amount2', index='account_id', columns='type1')\n",
    "data_4temp5.fillna(0, inplace=True)\n",
    "data_4temp5['r_out_in'] = data_4temp5[['out', 'income']].apply(lambda x: x.iloc[0] / x.iloc[1], axis=1)\n",
    "data_4temp5.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.470166Z",
     "end_time": "2024-06-18T16:36:19.606098Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id        date  amount  duration  payments status  \\\n0     5314        1787  1993-07-05   96396        12      8033      B   \n1     5316        1801  1993-07-11  165960        36      4610      A   \n2     6863        9188  1993-07-28  127080        60      2118      A   \n3     5325        1843  1993-08-03  105804        36      2939      A   \n4     7240       11013  1993-09-06  274740        60      4579      A   \n\n   bad_good  disp_id  client_id  ...   A13  A14   A15   a16   avg_balance  \\\n0         1     2166       2166  ...  3.67  100  15.7  14.8  12250.000000   \n1         0     2181       2181  ...  2.31  117  12.7  11.6  43975.810811   \n2         0    11006      11314  ...  2.89  132  13.3  13.6  30061.041667   \n3         0     2235       2235  ...  1.71  135  18.6  17.7  41297.640000   \n4         0    13231      13539  ...  4.52  110   9.0   8.4  49780.777778   \n\n   stdev_balance  cv_balance    income       out  r_out_in  \n0    8330.866301    0.680071   20100.0       0.0  0.000000  \n1   25468.748605    0.579154  243576.0  164004.0  0.673318  \n2   11520.127013    0.383224   75146.0   54873.0  0.730219  \n3   14151.357776    0.342667  120310.0   86018.0  0.714970  \n4   22172.541600    0.445404  276327.0  235214.0  0.851216  \n\n[5 rows x 30 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>status</th>\n      <th>bad_good</th>\n      <th>disp_id</th>\n      <th>client_id</th>\n      <th>...</th>\n      <th>A13</th>\n      <th>A14</th>\n      <th>A15</th>\n      <th>a16</th>\n      <th>avg_balance</th>\n      <th>stdev_balance</th>\n      <th>cv_balance</th>\n      <th>income</th>\n      <th>out</th>\n      <th>r_out_in</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>1993-07-05</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>B</td>\n      <td>1</td>\n      <td>2166</td>\n      <td>2166</td>\n      <td>...</td>\n      <td>3.67</td>\n      <td>100</td>\n      <td>15.7</td>\n      <td>14.8</td>\n      <td>12250.000000</td>\n      <td>8330.866301</td>\n      <td>0.680071</td>\n      <td>20100.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>1993-07-11</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2181</td>\n      <td>2181</td>\n      <td>...</td>\n      <td>2.31</td>\n      <td>117</td>\n      <td>12.7</td>\n      <td>11.6</td>\n      <td>43975.810811</td>\n      <td>25468.748605</td>\n      <td>0.579154</td>\n      <td>243576.0</td>\n      <td>164004.0</td>\n      <td>0.673318</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>1993-07-28</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>A</td>\n      <td>0</td>\n      <td>11006</td>\n      <td>11314</td>\n      <td>...</td>\n      <td>2.89</td>\n      <td>132</td>\n      <td>13.3</td>\n      <td>13.6</td>\n      <td>30061.041667</td>\n      <td>11520.127013</td>\n      <td>0.383224</td>\n      <td>75146.0</td>\n      <td>54873.0</td>\n      <td>0.730219</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>1993-08-03</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2235</td>\n      <td>2235</td>\n      <td>...</td>\n      <td>1.71</td>\n      <td>135</td>\n      <td>18.6</td>\n      <td>17.7</td>\n      <td>41297.640000</td>\n      <td>14151.357776</td>\n      <td>0.342667</td>\n      <td>120310.0</td>\n      <td>86018.0</td>\n      <td>0.714970</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>1993-09-06</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>A</td>\n      <td>0</td>\n      <td>13231</td>\n      <td>13539</td>\n      <td>...</td>\n      <td>4.52</td>\n      <td>110</td>\n      <td>9.0</td>\n      <td>8.4</td>\n      <td>49780.777778</td>\n      <td>22172.541600</td>\n      <td>0.445404</td>\n      <td>276327.0</td>\n      <td>235214.0</td>\n      <td>0.851216</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 30 columns</p>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data4 = pd.merge(data3, data_4temp3, left_on='account_id', right_index=True, how='left')\n",
    "data4 = pd.merge(data4, data_4temp5, left_on='account_id', right_index=True, how='left')\n",
    "data4.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.497271Z",
     "end_time": "2024-06-18T16:36:19.642093Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id        date  amount  duration  payments status  \\\n0     5314        1787  1993-07-05   96396        12      8033      B   \n1     5316        1801  1993-07-11  165960        36      4610      A   \n2     6863        9188  1993-07-28  127080        60      2118      A   \n3     5325        1843  1993-08-03  105804        36      2939      A   \n4     7240       11013  1993-09-06  274740        60      4579      A   \n\n   bad_good  disp_id  client_id  ...   A15   a16   avg_balance  stdev_balance  \\\n0         1     2166       2166  ...  15.7  14.8  12250.000000    8330.866301   \n1         0     2181       2181  ...  12.7  11.6  43975.810811   25468.748605   \n2         0    11006      11314  ...  13.3  13.6  30061.041667   11520.127013   \n3         0     2235       2235  ...  18.6  17.7  41297.640000   14151.357776   \n4         0    13231      13539  ...   9.0   8.4  49780.777778   22172.541600   \n\n   cv_balance    income       out  r_out_in      r_lb  r_lincome  \n0    0.680071   20100.0       0.0  0.000000  7.869061   4.795821  \n1    0.579154  243576.0  164004.0  0.673318  3.773893   0.681348  \n2    0.383224   75146.0   54873.0  0.730219  4.227398   1.691108  \n3    0.342667  120310.0   86018.0  0.714970  2.561987   0.879428  \n4    0.445404  276327.0  235214.0  0.851216  5.518998   0.994257  \n\n[5 rows x 32 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>date</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>status</th>\n      <th>bad_good</th>\n      <th>disp_id</th>\n      <th>client_id</th>\n      <th>...</th>\n      <th>A15</th>\n      <th>a16</th>\n      <th>avg_balance</th>\n      <th>stdev_balance</th>\n      <th>cv_balance</th>\n      <th>income</th>\n      <th>out</th>\n      <th>r_out_in</th>\n      <th>r_lb</th>\n      <th>r_lincome</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>1993-07-05</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>B</td>\n      <td>1</td>\n      <td>2166</td>\n      <td>2166</td>\n      <td>...</td>\n      <td>15.7</td>\n      <td>14.8</td>\n      <td>12250.000000</td>\n      <td>8330.866301</td>\n      <td>0.680071</td>\n      <td>20100.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n      <td>7.869061</td>\n      <td>4.795821</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>1993-07-11</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2181</td>\n      <td>2181</td>\n      <td>...</td>\n      <td>12.7</td>\n      <td>11.6</td>\n      <td>43975.810811</td>\n      <td>25468.748605</td>\n      <td>0.579154</td>\n      <td>243576.0</td>\n      <td>164004.0</td>\n      <td>0.673318</td>\n      <td>3.773893</td>\n      <td>0.681348</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>1993-07-28</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>A</td>\n      <td>0</td>\n      <td>11006</td>\n      <td>11314</td>\n      <td>...</td>\n      <td>13.3</td>\n      <td>13.6</td>\n      <td>30061.041667</td>\n      <td>11520.127013</td>\n      <td>0.383224</td>\n      <td>75146.0</td>\n      <td>54873.0</td>\n      <td>0.730219</td>\n      <td>4.227398</td>\n      <td>1.691108</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>1993-08-03</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>A</td>\n      <td>0</td>\n      <td>2235</td>\n      <td>2235</td>\n      <td>...</td>\n      <td>18.6</td>\n      <td>17.7</td>\n      <td>41297.640000</td>\n      <td>14151.357776</td>\n      <td>0.342667</td>\n      <td>120310.0</td>\n      <td>86018.0</td>\n      <td>0.714970</td>\n      <td>2.561987</td>\n      <td>0.879428</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>1993-09-06</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>A</td>\n      <td>0</td>\n      <td>13231</td>\n      <td>13539</td>\n      <td>...</td>\n      <td>9.0</td>\n      <td>8.4</td>\n      <td>49780.777778</td>\n      <td>22172.541600</td>\n      <td>0.445404</td>\n      <td>276327.0</td>\n      <td>235214.0</td>\n      <td>0.851216</td>\n      <td>5.518998</td>\n      <td>0.994257</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 32 columns</p>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1.6 计算贷存比，贷收比\n",
    "data4['r_lb'] = data4[['amount', 'avg_balance']].apply(lambda x: x.iloc[0] / x.iloc[1], axis=1)\n",
    "data4['r_lincome'] = data4[['amount', 'income']].apply(lambda x: x.iloc[0] / x.iloc[1], axis=1)\n",
    "data4.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.528097Z",
     "end_time": "2024-06-18T16:36:19.717099Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "Index(['loan_id', 'account_id', 'date', 'amount', 'duration', 'payments',\n       'status', 'bad_good', 'disp_id', 'client_id', 'type', 'sex',\n       'birth_date', 'district_id', 'A1', 'GDP', 'A4', 'A10', 'A11', 'A12',\n       'A13', 'A14', 'A15', 'a16', 'avg_balance', 'stdev_balance',\n       'cv_balance', 'income', 'out', 'r_out_in', 'r_lb', 'r_lincome'],\n      dtype='object')"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data4.columns"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.562198Z",
     "end_time": "2024-06-18T16:36:19.749095Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.569749Z",
     "end_time": "2024-06-18T16:36:19.786111Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "   loan_id  account_id  amount  duration  payments  disp_id  client_id  sex  \\\n0     5314        1787   96396        12      8033     2166       2166    0   \n1     5316        1801  165960        36      4610     2181       2181    1   \n2     6863        9188  127080        60      2118    11006      11314    1   \n3     5325        1843  105804        36      2939     2235       2235    0   \n4     7240       11013  274740        60      4579    13231      13539    1   \n\n   district_id  A1  ...  A14   a16   avg_balance  stdev_balance  cv_balance  \\\n0           30  30  ...  100  14.8  12250.000000    8330.866301    0.680071   \n1           46  46  ...  117  11.6  43975.810811   25468.748605    0.579154   \n2           45  45  ...  132  13.6  30061.041667   11520.127013    0.383224   \n3           14  14  ...  135  17.7  41297.640000   14151.357776    0.342667   \n4           63  63  ...  110   8.4  49780.777778   22172.541600    0.445404   \n\n     income       out  r_out_in      r_lb  r_lincome  \n0   20100.0       0.0  0.000000  7.869061   4.795821  \n1  243576.0  164004.0  0.673318  3.773893   0.681348  \n2   75146.0   54873.0  0.730219  4.227398   1.691108  \n3  120310.0   86018.0  0.714970  2.561987   0.879428  \n4  276327.0  235214.0  0.851216  5.518998   0.994257  \n\n[5 rows x 25 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>loan_id</th>\n      <th>account_id</th>\n      <th>amount</th>\n      <th>duration</th>\n      <th>payments</th>\n      <th>disp_id</th>\n      <th>client_id</th>\n      <th>sex</th>\n      <th>district_id</th>\n      <th>A1</th>\n      <th>...</th>\n      <th>A14</th>\n      <th>a16</th>\n      <th>avg_balance</th>\n      <th>stdev_balance</th>\n      <th>cv_balance</th>\n      <th>income</th>\n      <th>out</th>\n      <th>r_out_in</th>\n      <th>r_lb</th>\n      <th>r_lincome</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5314</td>\n      <td>1787</td>\n      <td>96396</td>\n      <td>12</td>\n      <td>8033</td>\n      <td>2166</td>\n      <td>2166</td>\n      <td>0</td>\n      <td>30</td>\n      <td>30</td>\n      <td>...</td>\n      <td>100</td>\n      <td>14.8</td>\n      <td>12250.000000</td>\n      <td>8330.866301</td>\n      <td>0.680071</td>\n      <td>20100.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n      <td>7.869061</td>\n      <td>4.795821</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5316</td>\n      <td>1801</td>\n      <td>165960</td>\n      <td>36</td>\n      <td>4610</td>\n      <td>2181</td>\n      <td>2181</td>\n      <td>1</td>\n      <td>46</td>\n      <td>46</td>\n      <td>...</td>\n      <td>117</td>\n      <td>11.6</td>\n      <td>43975.810811</td>\n      <td>25468.748605</td>\n      <td>0.579154</td>\n      <td>243576.0</td>\n      <td>164004.0</td>\n      <td>0.673318</td>\n      <td>3.773893</td>\n      <td>0.681348</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6863</td>\n      <td>9188</td>\n      <td>127080</td>\n      <td>60</td>\n      <td>2118</td>\n      <td>11006</td>\n      <td>11314</td>\n      <td>1</td>\n      <td>45</td>\n      <td>45</td>\n      <td>...</td>\n      <td>132</td>\n      <td>13.6</td>\n      <td>30061.041667</td>\n      <td>11520.127013</td>\n      <td>0.383224</td>\n      <td>75146.0</td>\n      <td>54873.0</td>\n      <td>0.730219</td>\n      <td>4.227398</td>\n      <td>1.691108</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>5325</td>\n      <td>1843</td>\n      <td>105804</td>\n      <td>36</td>\n      <td>2939</td>\n      <td>2235</td>\n      <td>2235</td>\n      <td>0</td>\n      <td>14</td>\n      <td>14</td>\n      <td>...</td>\n      <td>135</td>\n      <td>17.7</td>\n      <td>41297.640000</td>\n      <td>14151.357776</td>\n      <td>0.342667</td>\n      <td>120310.0</td>\n      <td>86018.0</td>\n      <td>0.714970</td>\n      <td>2.561987</td>\n      <td>0.879428</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7240</td>\n      <td>11013</td>\n      <td>274740</td>\n      <td>60</td>\n      <td>4579</td>\n      <td>13231</td>\n      <td>13539</td>\n      <td>1</td>\n      <td>63</td>\n      <td>63</td>\n      <td>...</td>\n      <td>110</td>\n      <td>8.4</td>\n      <td>49780.777778</td>\n      <td>22172.541600</td>\n      <td>0.445404</td>\n      <td>276327.0</td>\n      <td>235214.0</td>\n      <td>0.851216</td>\n      <td>5.518998</td>\n      <td>0.994257</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 25 columns</p>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = data4.drop('status', axis=1)\n",
    "X = X.drop('bad_good', axis=1)\n",
    "X = X.drop('type', axis=1)\n",
    "X = X.drop('date', axis=1)\n",
    "X = X.drop('birth_date', axis=1)\n",
    "X = X.drop('A12', axis=1)\n",
    "X = X.drop('A15', axis=1)\n",
    "X['sex'] = X['sex'].apply(lambda x: 1 if x == '男' else 0)\n",
    "# X['date'] = X['date'].apply(lambda x: x.timestamp())\n",
    "# X['birth_date'] = X['birth_date'].apply(lambda x: x.timestamp())\n",
    "X.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.575891Z",
     "end_time": "2024-06-18T16:36:19.788101Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "准确率: 0.6642335766423357\n",
      "\n",
      "混淆矩阵:\n",
      " [[19  2 18]\n",
      " [ 7  5  6]\n",
      " [12  1 67]]\n",
      "\n",
      "分类报告:\n",
      "               precision    recall  f1-score   support\n",
      "\n",
      "           0       0.50      0.49      0.49        39\n",
      "           1       0.62      0.28      0.38        18\n",
      "           2       0.74      0.84      0.78        80\n",
      "\n",
      "    accuracy                           0.66       137\n",
      "   macro avg       0.62      0.53      0.55       137\n",
      "weighted avg       0.65      0.66      0.65       137\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import accuracy_score, confusion_matrix, classification_report\n",
    "\n",
    "y = data4['bad_good']\n",
    "\n",
    "# 划分训练集和测试集\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "# 特征标准化\n",
    "scaler = StandardScaler()\n",
    "X_train = scaler.fit_transform(X_train)\n",
    "X_test = scaler.transform(X_test)\n",
    "\n",
    "# 创建并拟合模型\n",
    "model = LogisticRegression()\n",
    "model.fit(X_train, y_train)\n",
    "\n",
    "# 预测\n",
    "y_pred = model.predict(X_test)\n",
    "\n",
    "# 评估模型\n",
    "print(\"准确率:\", accuracy_score(y_test, y_pred))\n",
    "print(\"\\n混淆矩阵:\\n\", confusion_matrix(y_test, y_pred))\n",
    "print(\"\\n分类报告:\\n\", classification_report(y_test, y_pred))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T16:36:19.607098Z",
     "end_time": "2024-06-18T16:36:20.489831Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
